package album;

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Vector;

import com.oreilly.servlet.MultipartRequest;
import com.oreilly.servlet.multipart.DefaultFileRenamePolicy;

public class Album {
	
	// 페이지분리 관련된 필드
	// 페이지 분리 관련 필드 
	private int listSize = 10; // 한 페이지에 보여질 행 수
	private int blockSize = 2; // 한 번에 보여질 페이지 번호 수 (1, 2). 2개씩

	private int totSize = 0; // 검색된 데이터의 총 갯수 
	private int totPage = 0; // 전체 페이지 수
	private int totBlock = 0; // 전체 블럭수

	private int nowPage = 1; // 현재 페이지
	private int nowBlock = 1; // 현재 블럭

	private int startNo = 0; // 리스트 목록의 시작위치
	private int endNo = 0; // 리스트 목록의 마지막위치

	private int startPage = 0; // 한블럭에 표시할 시작 페이지 번호
	private int endPage = 0; // 한블럭에 표시할 마지막 페이지 번호
	
	
	//첨부파일
	int fileSize = 10*1024*1024;
	private String uploadPath = "";/*"C:\\workstory\\web1403\\WebContent\\WebContent\\images";*/
	String fileEncoding = "UTF-8";
	
	String driver = "oracle.jdbc.driver.OracleDriver";
	String url = "jdbc:oracle:thin:@192.168.10.103:1521:xe";
	String db_mid = "hr";
	String db_pwd = "hr";
	
	Connection conn;
	private AlbumVo vo;
	
	public Album(){// 매게변수가 있으면 안된다!!
		try {
			// 1) 드라이버 로딩
			Class.forName(driver).newInstance();
			System.out.println("드라이브 로딩...");
			// 2) Connection 객체 생성
			conn = DriverManager.getConnection(url, db_mid, db_pwd);
			System.out.println("Connection 성공!!");
		} catch (Exception ex) {
			ex.printStackTrace();
		}
	}
	
	
	public Vector<AlbumVo> list(){
		Vector<AlbumVo> v = new Vector<>();
		String sql = "";
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try{
			sql = "select count(serial) from album "
					+ "where mid like ? or subject like ? or content like ?";
			
			ps = conn.prepareStatement(sql);

			
			ps.setString(1, "%" + vo.getFind() + "%");
			ps.setString(2, "%" + vo.getFind() + "%");
			ps.setString(3, "%" + vo.getFind() + "%");
			
			rs = ps.executeQuery();
			
			if(rs.next()){
				
				setTotSize(rs.getInt(1));
				computePage();
			}
			
			sql = "select * from(select rownum no, a.* from "
					+ "(select * from album where (mid like ? or subject like ? or content like ?) "
					+ "order by mdate desc)a )c "
					+ "left outer join albumfile b on c.serial = b.fileid where no between ? and ?";
			
			ps = conn.prepareStatement(sql);
			
			
			ps.setString(1, "%" + vo.getFind() + "%");
			ps.setString(2, "%" + vo.getFind() + "%");
			ps.setString(3, "%" + vo.getFind() + "%");
			ps.setInt(4, startNo);
			ps.setInt(5, endNo);
			
			rs = ps.executeQuery();
			
			System.out.println("된당!");
			
			while(rs.next()){
		
				AlbumVo vo = new AlbumVo();
				vo.setSerial(rs.getInt("serial"));
				vo.setMid(rs.getString("mid"));
				vo.setSubject(rs.getString("subject"));
				vo.setContent(rs.getString("content"));
				vo.setMdate(rs.getDate("mdate"));
				vo.setFilename(rs.getString("filename"));

				
				v.add(vo);
			}
			
			rs.close();
			ps.close();
			conn.close();
			
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return v;
	}
	
	public boolean insert(){
		boolean flag=false;
		try{
			// 상대결로!!
			String str = vo.getRequest().getServletContext().getRealPath("WebContent");
			System.out.println(str);
			str = str.replace("\\WebContent", "");
			System.out.println(str);
			String pro = str.substring(str.lastIndexOf("\\") + 1, str.length());
			
			str = str.substring(0, str.indexOf(".")) + pro + "\\WebContent\\images"; 
			System.out.println(str);
			setUploadPath(str);
			
			MultipartRequest mr = new MultipartRequest(vo.getRequest(), uploadPath, fileSize, fileEncoding, new DefaultFileRenamePolicy());
			//업로드한 결과를 변수 처리!!
			String mid = mr.getParameter("mid");
			String subject = mr.getParameter("subject");
			String content = mr.getParameter("content");
			String sysfile = mr.getFilesystemName("filename");
			
			String sql = "insert all into album(serial, mid, subject, content, mdate)values(album_serial.nextval, ?,?,?,sysdate)";
			
			if(sysfile != null){
				sql += "into albumfile(serial, fileid, filename)values(afile_serial.nextval, album_serial.currval,?)";
			}
			sql += "select * from dual";
			
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, mid);
			ps.setString(2, subject);
			ps.setString(3, content);
			
			if(sysfile != null){
				ps.setString(4, sysfile);
			}
			if(ps.executeUpdate() > 0) flag = true;
			
			ps.close();
			conn.close();
			
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return flag;
	}
	
	
	public AlbumVo view(){
		AlbumVo v = null;
		String sql = "select * from album a left outer join albumfile b on a.serial = b.fileid where a.serial = ?";
		try{
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1, vo.getSerial());
			
			ResultSet rs = ps.executeQuery();
			if(rs.next()){
				v = new AlbumVo();
				v.setSerial(rs.getInt("serial"));
				v.setMid(rs.getString("mid"));
				v.setSubject(rs.getString("subject"));
				v.setContent(rs.getString("content"));
				v.setMdate(rs.getDate("mdate"));
				v.setFilename(rs.getString("filename"));
			}
			rs.close();
			ps.close();
			conn.close();
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return v;
	}
	
	public void fileDel(String oldfile, int serial){
		String sql = "delete from albumfile where fileid=? and filename=?";
		try{
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1, serial);
			ps.setString(2, oldfile);
			int cnt = ps.executeUpdate();
			
			if(cnt>0){
				File file = new File(uploadPath + "/" + oldfile);
				if(file.exists()) file.delete();
			}
		}catch(Exception ex){
			ex.printStackTrace();
		}	
		
	}
	
	public boolean modify(){
		boolean flag=false;
		try{
			
			// 상대결로!!
			String str = vo.getRequest().getServletContext().getRealPath("WebContent");
			System.out.println(str);
			str = str.replace("\\WebContent", "");
			System.out.println(str);
			String pro = str.substring(str.lastIndexOf("\\") + 1, str.length());
					
			str = str.substring(0, str.indexOf(".")) + pro + "\\WebContent\\images"; 
			System.out.println(str);
			setUploadPath(str);
			
			
			MultipartRequest mr = new MultipartRequest(vo.getRequest(), uploadPath, fileSize, fileEncoding, new DefaultFileRenamePolicy());
			String mid = mr.getParameter("mid");
			String subject = mr.getParameter("subject");
			String content = mr.getParameter("content");
			String filename = mr.getParameter("filename");
			String sysfile = mr.getFilesystemName("newfilename");
			int serial = Integer.parseInt(mr.getParameter("serial"));
			String fileDelFlag = mr.getParameter("fileDelFlag");
			
			System.out.println(filename);
			System.out.println(sysfile);
			System.out.println(serial);
			System.out.println(fileDelFlag);
			
			PreparedStatement ps = null;
			String sql = null;
			int cnt = 0;
			
			// 자동 Commit 방지!
			conn.setAutoCommit(false);
			
			// 기존의 첨부된 파일을 삭제
			if(fileDelFlag.equals("del")){
				fileDel(filename, serial);
			}
			
			// 새로운 첨부파일이 존재하지 않으면..(내용만 수정된 경우!)
			
			sql = "update album set subject=?, content=? where serial=?";
			ps = conn.prepareStatement(sql);
			ps.setString(1, subject);
			ps.setString(2, content);
			ps.setInt(3, serial);
			cnt = ps.executeUpdate();
			
			if (cnt > 0) flag = true;
			
			// 새로운 첨부파일이 생성된 경우(내용+첨부파일이 모두 수정된경우)
			if(sysfile != null && sysfile != ""){
				sql="insert into albumfile(serial, fileid, filename)values(afile_serial.nextval,?,?)";
				ps = conn.prepareStatement(sql);
				ps.setInt(1, serial);
				ps.setString(2, sysfile);
				
				cnt = ps.executeUpdate();
				if(cnt>0){
					conn.commit();
					flag=true;
				}else{
					conn.rollback();
				}	
			}
			
			ps.close();
			conn.close();
			
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return flag;
	}
	
	public boolean delete(){
		boolean flag = false;
		String sql = "delete from album where serial=?";
		try{
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1, vo.getSerial());
			fileDel(vo.getFilename(), vo.getSerial());
			int cnt = ps.executeUpdate();
			if(cnt>0){
				flag=true;
				File file = new File(uploadPath + "/" + vo.getFilename());
				if(file.exists()){
					file.delete();
				}
			}
			
		}catch(Exception ex){
			ex.printStackTrace();
		}
		
		return flag;
	}
	
	
	public void computePage(){
		totPage = (int)Math.ceil(totSize*1.0 / listSize);
		totBlock = (int)Math.ceil(totPage*1.0 / blockSize);
		
		nowBlock = (int)Math.ceil(nowPage*1.0 / blockSize);
		
		endPage = nowBlock * blockSize;
		startPage = endPage - blockSize + 1;
		
		if(totPage < endPage) endPage = totPage;
		
		endNo = nowPage * listSize; 
		startNo = endNo - listSize + 1;
		
		if(totSize < endNo) endNo = totSize;
	}
	
	
	
	public int getBlockSize() {
		return blockSize;
	}


	public void setBlockSize(int blockSize) {
		this.blockSize = blockSize;
	}


	public int getTotSize() {
		return totSize;
	}


	public void setTotSize(int totSize) {
		this.totSize = totSize;
	}


	public int getTotPage() {
		return totPage;
	}


	public void setTotPage(int totPage) {
		this.totPage = totPage;
	}


	public int getTotBlock() {
		return totBlock;
	}


	public void setTotBlock(int totBlock) {
		this.totBlock = totBlock;
	}


	public int getNowPage() {
		return nowPage;
	}


	public void setNowPage(int nowPage) {
		this.nowPage = nowPage;
	}


	public int getNowBlock() {
		return nowBlock;
	}


	public void setNowBlock(int nowBlock) {
		this.nowBlock = nowBlock;
	}


	public int getStartNo() {
		return startNo;
	}


	public void setStartNo(int startNo) {
		this.startNo = startNo;
	}


	public int getEndNo() {
		return endNo;
	}


	public void setEndNo(int endNo) {
		this.endNo = endNo;
	}


	public int getListSize() {
		return listSize;
	}


	public void setListSize(int listSize) {
		this.listSize = listSize;
	}


	public int getStartPage() {
		return startPage;
	}


	public void setStartPage(int startPage) {
		this.startPage = startPage;
	}


	public int getEndPage() {
		return endPage;
	}


	public void setEndPage(int endPage) {
		this.endPage = endPage;
	}


	public AlbumVo getVo() {
		return vo;
	}


	public void setVo(AlbumVo vo) {
		this.vo = vo;
	}


	public String getUploadPath() {
		return uploadPath;
	}


	public void setUploadPath(String uploadPath) {
		this.uploadPath = uploadPath;
	}
	
	
}
